package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select\n" +
            "    ci.chapter_name  `name`,\n" +
            "    count(u.id) `num`,\n" +
            "    sum(u.position_sec) `time`,\n" +
            "    count(distinct u.user_id) `count`,\n" +
            "    sum(u.position_sec)/count(distinct u.user_id) `avgTime`\n" +
            "from user_chapter_process u\n" +
            "left join chapter_info ci on ci.id = u.chapter_id\n" +
            "group by ci.chapter_name")
    List<Vo1> getlist();

    @Select("with a as\n" +
            "(select\n" +
            "    case\n" +
            "    when score <60 then '[0,60)'\n" +
            "    when score >=60 and score <80 then '[60,80)'\n" +
            "    when score >=80 and score <90 then '[80,90)'\n" +
            "    when score >=90 and score <95 then '[90,95)'\n" +
            "    when score >=95  then '[95,100]'\n" +
            "    end `name`\n" +
            "    from test_exam)select a.name,count(*) `value` from a group by  a.name")
    List<Vo> getlist2();

    @Select("select bp.name,sum(oi.final_amount) `value`  from order_info oi\n" +
            "left join  base_province bp on oi.province_id = bp.id\n" +
            "group by bp.name;")
    List<Vo> getlist3();

    @Select("select sum(final_amount)  from order_info")
    Double getlist4();

    @Select("select hour(create_time) `name`,count(id) `value`\n" +
            "from user_chapter_process group by hour(create_time) order by hour(create_time)")
    List<Vo> getlist5();

    @Select("select bs.source_site `name`,sum(oi.final_amount) `price` ,count(distinct oi.user_id) `num`, count(oi.id) `count` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "left join chapter_info c on ci.id = c.course_id\n" +
            "left join video_info vi on c.id = vi.chapter_id\n" +
            "left join base_source bs on vi.video_source_id = bs.id group by bs.source_site")
    List<Vo2> getlist6();

    @Select("select count(*) from order_info where date(create_time)=date(now())")
    Double getlist7();

    @Select("select ci.course_name `name`,sum(oi.final_amount) `value` from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join course_info ci on ci.id = od.course_id\n" +
            "group by ci.course_name")
    List<Vo> getlist8();
}
